package com.kaluli.project.repository.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.kaluli.project.domain.entity.CpsVO;
import com.kaluli.project.domain.entity.KllOrderDataVo;
import com.kaluli.project.domain.entity.KllUserDataVo;
import com.kaluli.project.domain.entity.KllUserFormVo;
import com.kaluli.project.domain.repository.KllDataRepository;
import com.kaluli.project.domain.repository.KllUserDataRepository;
import com.kaluli.system.repository.AbstractRepositorySupport;

@Repository
public class KllUserDataRepositoryImpl extends AbstractRepositorySupport<KllUserFormVo> implements KllUserDataRepository{

	@Override
	protected Class<KllUserFormVo> getEntityClass() {
		// TODO Auto-generated method stub
		return KllUserFormVo.class;
	}

	/**
	 * 卡路里注册用户
	 */
	@Override
	public List<KllUserFormVo> findUserByKaluli(String type) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) AS count,u.user_id as id FROM kll_user u WHERE source !=0 ");
		
		//今日新增的用户数
		if(type.equals("todayadd")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(ct_time))=0 ");
		//3个月内没有访问过网站的用户总数
		}else if(type.equals("lastlogin")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(last_login_time))=0 ");
		}else if(type.equals("lost")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(u.last_login_time))>90 ");
		}else if(type.equals("lostToday")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(u.last_login_time))=90 ");
		}else if(type.equals("weekadd")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(ct_time))>=0 and DATEDIFF(NOW(),FROM_UNIXTIME(ct_time))<7 ");
		}
		
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	
	/**
	 * 卡路里注册用户
	 */
	public List<KllUserFormVo> findUserByKaluliWeek(String source) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) AS count,u.user_id as id FROM kll_user u WHERE 1=1 ");
		
		sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(ct_time))>=0 and DATEDIFF(NOW(),FROM_UNIXTIME(ct_time))<7 ");
		if(source.equals("PC")){
			sql.append(" and u.source =1 ");
		}else if(source.equals("M")){
			sql.append(" and u.source =2 ");
		}
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	/**
	 * 虎扑来源用户(下单即为用户)
	 */
	@Override
	public List<KllUserFormVo> findUserByHupu(String type) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) AS count,u.user_id as id FROM kll_user u,kll_main_order m ");
		sql.append(" WHERE u.`user_id` = m.`hupu_uid`  AND u.source=0 ");
		
		//今日新增的用户数
		if(type.equals("payer")){
			sql.append(" and DATEDIFF(NOW(),m.`order_time`)=0 and DATEDIFF(FROM_UNIXTIME(u.ct_time),NOW())=0  AND m.`pay_time` IS NOT NULL");
		}else if(type.equals("lastlogin")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(u.last_login_time))=0  ");
		}else if(type.equals("todaypay")){
			sql.append(" AND m.`pay_time` IS NOT NULL ");
		}else if(type.equals("lost")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(u.last_login_time))>90 ");
		}else if(type.equals("lostToday")){
			sql.append(" and DATEDIFF(NOW(),FROM_UNIXTIME(u.last_login_time))=90 ");
		}
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		
		return list;
	}
	
	/**
	 * 当天Hupu下单用户
	 * @return
	 */
	public List<KllUserFormVo> findHupuUserByToday(String type) {
		StringBuffer sql = new StringBuffer(" SELECT u.user_id AS id ");
		sql.append(" FROM kll_user u,kll_main_order m  ");
		sql.append(" WHERE u.`user_id` = m.`hupu_uid`  AND u.source=0  ");
		sql.append(" AND DATEDIFF(NOW(),m.`order_time`)=0  ");
		if(type.equals("pay")){
			sql.append(" AND m.`pay_time` IS NOT NULL ");
		}
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	/**
	 * 当天Hupu下单用户 (过滤方法)
	 * @return
	 */
	public List<KllUserFormVo> deleteUserByOther(String uid,String type) {
		StringBuffer sql = new StringBuffer(" SELECT count(*) as count, id ");
		sql.append(" FROM kll_main_order m  ");
		sql.append(" WHERE 1=1  ");
		if(type.equals("week")){
			sql.append(" AND DATEDIFF(NOW(),m.`order_time`) >=7  ");
		}else if(type.equals("today")){
			sql.append(" AND DATEDIFF(NOW(),m.`order_time`) !=0  ");
		}
		sql.append(" AND m.hupu_uid =  "+uid);
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	
	/**
	 * 购物次数》3的用户数
	 * 135天内
	 * @return
	 */
	public List<KllUserFormVo> findUserByLoyal() {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) AS count, count(*) as id FROM ");
		sql.append("(SELECT COUNT(*) AS count,user_id as id FROM kll_user u,kll_main_order m WHERE u.`user_id` = m.`hupu_uid` AND m.`pay_time` IS NOT NULL AND DATEDIFF(NOW(),m.`order_time`)<135  GROUP BY m.`hupu_uid` HAVING  COUNT(*)>3) AS t	");
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	
	/**
	 * 下单人数/支付人数
	 * @return
	 */
	public List<KllUserFormVo> findUserByOrder(String type) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) AS COUNT,count(*) as id FROM ");
		sql.append("(SELECT COUNT(*) AS COUNT FROM kll_user u,kll_main_order m  WHERE u.`user_id` = m.`hupu_uid` ");
		if(type.equals("pay")){
			sql.append(" AND m.`pay_time` IS NOT NULL ");
		}
		sql.append(" GROUP BY u.`user_id`) t	");
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	/**
	 * 
	 * 
	 * 
	 */
	public List<KllUserFormVo> findOrderByWeek(String type,String source) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) AS count,u.user_id as id FROM kll_user u,kll_main_order m ");
		sql.append(" WHERE u.`user_id` = m.`hupu_uid` ");
		
		if(type.equals("30")){
			sql.append(" and DATEDIFF(NOW(),m.`order_time`)>=30 and DATEDIFF(NOW(),m.`order_time`)<37  AND m.`pay_time` IS NOT NULL");
		}else if(type.equals("45")){
			sql.append(" and DATEDIFF(NOW(),m.`order_time`)>=45 and DATEDIFF(NOW(),m.`order_time`)<52  AND m.`pay_time` IS NOT NULL");
		}else if(type.equals("60")){
			sql.append(" and DATEDIFF(NOW(),m.`order_time`)>=60 and DATEDIFF(NOW(),m.`order_time`)<67  AND m.`pay_time` IS NOT NULL");
		}else if(type.equals("90")){
			sql.append(" and DATEDIFF(NOW(),m.`order_time`)>=90 and DATEDIFF(NOW(),m.`order_time`)<97  AND m.`pay_time` IS NOT NULL");
		}
		if(source.equals("PC")){
			sql.append("  and u.source = 1 ");
		}else if(source.equals("M")){
			sql.append("  and u.source = 2 ");
		}
		sql.append(" group by u.user_id ");
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		
		return list;
	}
	
	
	/*
	 * 复购用户
	 * 
	 */
	public List<KllUserFormVo> findOrderWeekByPay(String num,String uid) {
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) AS count FROM kll_main_order m ");
		sql.append(" WHERE u.`user_id` = m.`hupu_uid` ");
		
		sql.append(" and DATEDIFF(NOW(),m.`order_time`)>=0 and DATEDIFF(NOW(),m.`order_time`)< "+num+"  AND m.`pay_time` IS NOT NULL");
		sql.append(" and m.hupu_uid ="+ uid);
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		
		return list;
	}
	
	
	/*
	 * 购物车下单成功人数
	 * 
	 */
	public List<KllUserFormVo> findShoppingOrder() {
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) as count,id FROM ");
		
		sql.append(" (SELECT COUNT(*) AS cnt,o.id FROM kll_main_order o ,kll_order_history h WHERE o.`order_number` = h.`order_number` ");
		sql.append(" and DATEDIFF(NOW(),o.`order_time`)=0 AND h.`type` = 0  AND h.`explanation` LIKE '%购物车%' AND o.`pay_time` IS NOT NULL  GROUP BY o.`hupu_uid`) t ");
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		
		return list;
	}
	
	
	/*
	 * 立即购买成功人数
	 * 
	 */
	public List<KllUserFormVo> findOrderBuyNow() {
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) as count,id FROM ");
		
		sql.append(" (SELECT COUNT(*) AS cnt,o.id FROM kll_main_order o ,kll_order_history h WHERE o.`order_number` = h.`order_number`  ");
		sql.append(" and DATEDIFF(NOW(),o.`order_time`)=0 AND h.`type` = 0 AND h.`explanation` NOT LIKE '%购物车%' AND o.`pay_time` IS NOT NULL GROUP BY o.`hupu_uid`) t ");
		
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		
		return list;
	}
	
	/**
	 * Cps中虎扑app用户数
	 * @return
	 */
	public List<KllUserFormVo> findhupuAppUser() {
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) as count,o.id  FROM order_cps_user u, kll_main_order o  where  ");
		sql.append(" u.`uid`=o.`hupu_uid`  and unionId = 'hupuappmore' ");
//		sql.append(" AND DATEDIFF(NOW(),o.`order_time`)>=0 and DATEDIFF(NOW(),o.`order_time`)<7 ");
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	
	/**
	 * 达人收货校验
	 * @return
	 */
	public List<KllUserFormVo> findDarenOrder(String orderNumber) {
		StringBuffer sql = new StringBuffer(" SELECT count(*) as count,o.id FROM kll_kol_order k,kll_order o WHERE k.order_number = o.`order_number` AND k.`order_number` =");
		sql.append(orderNumber);
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	/**
	 * 达人收货校验
	 * @return
	 */
	public List<KllUserFormVo> updateDarenOrder(String orderNumber) {
		StringBuffer sql = new StringBuffer(" update kll_kol_order set status =2 where order_number = ");
		sql.append(orderNumber);
		List<KllUserFormVo> list = excuteSql(sql.toString());
		if(list != null && list.size()>0){
			if(list.get(0) == null){
				return null;
			}
		}
		return list;
	}
	
	

	

}
